class WelcomeController < ApplicationController
  def index
    render :action=>"index", :layout=>"login_layout"
  end

  def index2
    render :text=>"Under Construction!"
  end

  def rungroup
    require 'spreadsheet'
    book = Spreadsheet::Workbook.new
    sheet1 = book.create_worksheet
    sheet1.name = 'Exported Data'
    current_row = 1
    row = sheet1.row(current_row)
    header_array = ["Game","Player","PlayerID","กู้ต่ำ","กู้สูง","มี warehouse","ไม่มี warehouse","1โรงงาน","2โรงงาน","ราคาต่ำ","ราคาสูง","ไม่ใช้ marketing","ใช้ marketing","กู้ต่ำ","กู้สูง","มี warehouse","ไม่มี warehouse","1โรงงาน","2โรงงาน","ราคาต่ำ","ราคาสูง","ไม่ใช้ marketing","ใช้ marketing","ออก SEO","ไม่ออก SEO","มี StockRepurchase","ไม่มี StockRepurchase","มี Debt Redeem","ไม่มี Debt Redeem","Y1 Inventoy Cost","Y1 Inventoy Unit","Y1 Cash","Y1 Retained Earning","Y2 Retained Earning","Y1 NI","Y2 NI"]
    row.replace header_array
    h1 = Hash.new
    h2 = Hash.new
    h3 = Hash.new
    h4 = Hash.new
    h5 = Hash.new
    h6 = Hash.new
    h7 = Hash.new
    h8 = Hash.new
    h9 = Hash.new
    h10 = Hash.new
    h11 = Hash.new
    h12 = Hash.new
    h13 = Hash.new
    h14 = Hash.new
    h15 = Hash.new
    h16 = Hash.new
    h17 = Hash.new
    h18 = Hash.new
    h19 = Hash.new
    h20 = Hash.new
    h21 = Hash.new
    h22 = Hash.new
    h23 = Hash.new
    h24 = Hash.new
    h25 = Hash.new
    h26 = Hash.new
    h27 = Hash.new
    h28 = Hash.new
    h29 = Hash.new
    h30 = Hash.new
    h31 = Hash.new
    h32 = Hash.new
    h33 = Hash.new
    h34 = Hash.new
    h35 = Hash.new
    h36 = Hash.new
    h37 = Hash.new
    h38 = Hash.new
    h39 = Hash.new
    h40 = Hash.new
    h41 = Hash.new
    h42 = Hash.new
    h43 = Hash.new
    h44 = Hash.new
    h45 = Hash.new
    h46 = Hash.new
    h47 = Hash.new
    h48 = Hash.new
    h49 = Hash.new
    h50 = Hash.new
    h51 = Hash.new
    h52 = Hash.new
    h53 = Hash.new
    h54 = Hash.new
    h55 = Hash.new
    h56 = Hash.new
    h57 = Hash.new
    h58 = Hash.new
    players = Player.find(:all, :conditions=>["game_id IN (16,25,34,43,45,51,55,57,60,66,68,77,85,93)"], :order=>"game_id,id")
    players.each do |p|
      pb1 = PlayerBalance.find(:first, :conditions=>["player_id=? AND year=1", p.id])
      pb2 = PlayerBalance.find(:first, :conditions=>["player_id=? AND year=2", p.id])
      current_row = current_row + 1
      row_array = Array.new
      row_array = row_array << p.game.name
      row_array = row_array << p.name
      row_array = row_array << "#{p.id}"
      debt1 = pb1.debt_interest.to_f / 0.05
      debt2 = pb2.debt_interest.to_f / 0.05
      board_data1 = Board.find(:first, :conditions=>["player_id=? AND year=?", p.id, 1]).board_data
      board_data2 = Board.find(:first, :conditions=>["player_id=? AND year=?", p.id, 2]).board_data
      if debt1<=20000000
        row_array = row_array << 1
        row_array = row_array << 0
        h1["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h2["#{p.id}"] = pb1.net_income
      end
      if f_warehouse(board_data1)
        row_array = row_array << 1
        row_array = row_array << 0
        h3["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h4["#{p.id}"] = pb1.net_income
      end
      if f_factory(board_data1)
        row_array = row_array << 1
        row_array = row_array << 0
        h5["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h6["#{p.id}"] = pb1.net_income
      end
      if below_average_price(p.id, 1)
        row_array = row_array << 1
        row_array = row_array << 0
        h7["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h8["#{p.id}"] = pb1.net_income
      end
      if pb1.marketing==0
        row_array = row_array << 1
        row_array = row_array << 0
        h9["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h10["#{p.id}"] = pb1.net_income
      end
      if debt2<=20000000
        row_array = row_array << 1
        row_array = row_array << 0
        h11["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h12["#{p.id}"] = pb1.net_income
      end
      if f_warehouse(board_data2)
        row_array = row_array << 1
        row_array = row_array << 0
        h13["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h14["#{p.id}"] = pb1.net_income
      end
      if f_factory(board_data2)
        row_array = row_array << 1
        row_array = row_array << 0
        h15["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h16["#{p.id}"] = pb1.net_income
      end
      if below_average_price(p.id, 2)
        row_array = row_array << 1
        row_array = row_array << 0
        h17["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h18["#{p.id}"] = pb1.net_income
      end
      if pb2.marketing==0
        row_array = row_array << 1
        row_array = row_array << 0
        h19["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h20["#{p.id}"] = pb1.net_income
      end
      if pb2.common_stock_unit>2000000
        row_array = row_array << 1
        row_array = row_array << 0
        h21["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h22["#{p.id}"] = pb1.net_income
      end
      if pb2.common_stock_unit<2000000
        row_array = row_array << 1
        row_array = row_array << 0
        h23["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h24["#{p.id}"] = pb1.net_income
      end
      if debt2<debt1
        row_array = row_array << 1
        row_array = row_array << 0
        h25["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h26["#{p.id}"] = pb1.net_income
      end
      row_array = row_array << pb1.inventory_cost
      row_array = row_array << pb1.inventory_unit
      row_array = row_array << pb1.cash
      row_array = row_array << pb1.retain_earning
      row_array = row_array << pb2.retain_earning
      row_array = row_array << pb1.net_income
      row_array = row_array << pb2.net_income
      row = sheet1.row(current_row)
      row.replace row_array

      if h3["#{p.id}"]
        if h1["#{p.id}"]
          if h7["#{p.id}"]
            h27["#{p.id}"] = pb1.net_income
          else
            h28["#{p.id}"] = pb1.net_income
          end
        else
          if h7["#{p.id}"]
            h29["#{p.id}"] = pb1.net_income
          else
            h30["#{p.id}"] = pb1.net_income
          end
        end
      else
        if h1["#{p.id}"]
          if h7["#{p.id}"]
            h31["#{p.id}"] = pb1.net_income
          else
            h32["#{p.id}"] = pb1.net_income
          end
        else
          if h7["#{p.id}"]
            h33["#{p.id}"] = pb1.net_income
          else
            h34["#{p.id}"] = pb1.net_income
          end
        end
      end

      if h13["#{p.id}"]
        if h11["#{p.id}"]
          if h17["#{p.id}"]
            h35["#{p.id}"] = pb2.net_income
          else
            h36["#{p.id}"] = pb2.net_income
          end
        else
          if h17["#{p.id}"]
            h37["#{p.id}"] = pb2.net_income
          else
            h38["#{p.id}"] = pb2.net_income
          end
        end
      else
        if h11["#{p.id}"]
          if h17["#{p.id}"]
            h39["#{p.id}"] = pb2.net_income
          else
            h40["#{p.id}"] = pb2.net_income
          end
        else
          if h17["#{p.id}"]
            h41["#{p.id}"] = pb2.net_income
          else
            h42["#{p.id}"] = pb2.net_income
          end
        end
      end
      
    end

    players = Player.find(:all, :conditions=>["game_id IN (21,26,49,65,75,79)"], :order=>"game_id,id")
    players.each do |p|
      pb1 = PlayerBalance.find(:first, :conditions=>["player_id=? AND year=1", p.id])
      current_row = current_row + 1
      row_array = Array.new
      row_array = row_array << p.game.name
      row_array = row_array << p.name
      row_array = row_array << "#{p.id}"
      debt1 = pb1.debt_interest.to_f / 0.05
      board_data1 = Board.find(:first, :conditions=>["player_id=? AND year=?", p.id, 1]).board_data
      if debt1<=20000000
        row_array = row_array << 1
        row_array = row_array << 0
        h1["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h2["#{p.id}"] = pb1.net_income
      end
      if f_warehouse(board_data1)
        row_array = row_array << 1
        row_array = row_array << 0
        h3["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h4["#{p.id}"] = pb1.net_income
      end
      if f_factory(board_data1)
        row_array = row_array << 1
        row_array = row_array << 0
        h5["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h6["#{p.id}"] = pb1.net_income
      end
      if below_average_price(p.id, 1)
        row_array = row_array << 1
        row_array = row_array << 0
        h7["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h8["#{p.id}"] = pb1.net_income
      end
      if pb1.marketing==0
        row_array = row_array << 1
        row_array = row_array << 0
        h9["#{p.id}"] = pb1.net_income
      else
        row_array = row_array << 0
        row_array = row_array << 1
        h10["#{p.id}"] = pb1.net_income
      end
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << ""
      row_array = row_array << pb1.inventory_cost
      row_array = row_array << pb1.inventory_unit
      row_array = row_array << pb1.cash
      row_array = row_array << pb1.retain_earning
      row_array = row_array << ""
      row_array = row_array << pb1.net_income
      row = sheet1.row(current_row)
      row.replace row_array

      if h3["#{p.id}"]
        if h1["#{p.id}"]
          if h7["#{p.id}"]
            h27["#{p.id}"] = pb1.net_income
          else
            h28["#{p.id}"] = pb1.net_income
          end
        else
          if h7["#{p.id}"]
            h29["#{p.id}"] = pb1.net_income
          else
            h30["#{p.id}"] = pb1.net_income
          end
        end
      else
        if h1["#{p.id}"]
          if h7["#{p.id}"]
            h31["#{p.id}"] = pb1.net_income
          else
            h32["#{p.id}"] = pb1.net_income
          end
        else
          if h7["#{p.id}"]
            h33["#{p.id}"] = pb1.net_income
          else
            h34["#{p.id}"] = pb1.net_income
          end
        end
      end
    end

    current_row += 2
    row_array1 = ["Y1 กู้ต่ำ (กลุ่ม)"]
    row_array2 = ["Y1 กู้ต่ำ (Value)"]
    h1.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y1 กู้สูง (กลุ่ม)"]
    row_array2 = ["Y1 กู้สูง (Value)"]
    h2.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y1 มี warehouse (กลุ่ม)"]
    row_array2 = ["Y1 มี warehouse (Value)"]
    h3.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y1 ไม่มี warehouse (กลุ่ม)"]
    row_array2 = ["Y1 ไม่มี warehouse (Value)"]
    h4.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y1 1โรงงาน (กลุ่ม)"]
    row_array2 = ["Y1 1โรงงาน (Value)"]
    h5.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y1 2โรงงาน (กลุ่ม)"]
    row_array2 = ["Y1 2โรงงาน (Value)"]
    h6.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y1 ราคาต่ำ (กลุ่ม)"]
    row_array2 = ["Y1 ราคาต่ำ (Value)"]
    h7.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y1 ราคาสูง (กลุ่ม)"]
    row_array2 = ["Y1 ราคาสูง (Value)"]
    h8.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y1 ไม่ใช้ marketing (กลุ่ม)"]
    row_array2 = ["Y1 ไม่ใช้ marketing (Value)"]
    h9.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y1 ใช้ marketing (กลุ่ม)"]
    row_array2 = ["Y1 ใช้ marketing (Value)"]
    h10.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 กู้ต่ำ (กลุ่ม)"]
    row_array2 = ["Y2 กู้ต่ำ (Value)"]
    h11.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 กู้สูง (กลุ่ม)"]
    row_array2 = ["Y2 กู้สูง (Value)"]
    h12.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 มี warehouse (กลุ่ม)"]
    row_array2 = ["Y2 มี warehouse (Value)"]
    h13.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 ไม่มี warehouse (กลุ่ม)"]
    row_array2 = ["Y2 ไม่มี warehouse (Value)"]
    h14.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 1โรงงาน (กลุ่ม)"]
    row_array2 = ["Y2 1โรงงาน (Value)"]
    h15.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 2โรงงาน (กลุ่ม)"]
    row_array2 = ["Y2 2โรงงาน (Value)"]
    h16.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 ราคาต่ำ (กลุ่ม)"]
    row_array2 = ["Y2 ราคาต่ำ (Value)"]
    h17.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 ราคาสูง (กลุ่ม)"]
    row_array2 = ["Y2 ราคาสูง (Value)"]
    h18.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 ไม่ใช้ marketing (กลุ่ม)"]
    row_array2 = ["Y2 ไม่ใช้ marketing (Value)"]
    h19.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 ใช้ marketing (กลุ่ม)"]
    row_array2 = ["Y2 ใช้ marketing (Value)"]
    h20.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 ออก SEO (กลุ่ม)"]
    row_array2 = ["Y2 ออก SEO (Value)"]
    h21.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 ไม่ออก SEO (กลุ่ม)"]
    row_array2 = ["Y2 ไม่ออก SEO (Value)"]
    h22.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 มี SR (กลุ่ม)"]
    row_array2 = ["Y2 มี SR (Value)"]
    h23.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 ไม่มี SR (กลุ่ม)"]
    row_array2 = ["Y2 ไม่มี SR (Value)"]
    h24.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 มี DR (กลุ่ม)"]
    row_array2 = ["Y2 มี DR (Value)"]
    h25.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    row_array1 = ["Y2 ไม่มี DR (กลุ่ม)"]
    row_array2 = ["Y2 ไม่มี DR (Value)"]
    h26.sort{|a,b| a[1]<=>b[1]}.each do |k,v|
      row_array1 = row_array1 << k
      row_array2 = row_array2 << v
    end
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array1
    current_row += 1
    row = sheet1.row(current_row)
    row.replace row_array2

    current_row += 2
    for i in 27..34 do
      row_array1 = ['Y1','Y','Y','Y','กลุ่ม']
      row_array2 = ['','','','','Value','']
      row_array1 = row_array1 << eval("h#{i}").size
      eval("h#{i}").sort{|a,b| a[1]<=>b[1]}.each do |k,v|
        row_array1 = row_array1 << k
        row_array2 = row_array2 << v
      end
      current_row += 1
      row = sheet1.row(current_row)
      row.replace row_array1
      current_row += 1
      row = sheet1.row(current_row)
      row.replace row_array2
    end

    current_row += 2
    for i in 35..42 do
      row_array1 = ['Y2','Y','Y','Y','กลุ่ม']
      row_array2 = ['','','','','Value','']
      row_array1 = row_array1 << eval("h#{i}").size
      eval("h#{i}").sort{|a,b| a[1]<=>b[1]}.each do |k,v|
        row_array1 = row_array1 << k
        row_array2 = row_array2 << v
      end
      current_row += 1
      row = sheet1.row(current_row)
      row.replace row_array1
      current_row += 1
      row = sheet1.row(current_row)
      row.replace row_array2
    end

    path = "#{RAILS_ROOT}/public/group2.xls"
    book.write path
    data = read_binary(path)
    send_data(data, :filename=>'group.xls', :type=>'application/vnd.ms-excel', :disposition=>"attachment")
  end

  def f_warehouse(board_data)
    i1 = board_data.index("<br/>Warehouses<br/>")
    i2 = board_data.index("<br/>Factories<br/>")
    word = board_data[i1..i2]
    if word.include? '<td>1</td>'
      return true
    else
      return false
    end
  end

  def f_factory(board_data)
    i1 = board_data.index("<br/>Factories<br/>")
    word = board_data[i1..(board_data.size-1)]
    count = 0
    if word.include? '<td>1</td>'
      count += 1
    end
    if word.include? '<td>2</td>'
      count += 1
    end
    if count>1
      return false
    else
      return true
    end
  end

  def below_average_price(p_id, year)
    avg = Sale.average(:price, :conditions=>["player_id=? AND year=?", p_id, year])
    if avg
      if avg>25
        return false
      else
        return true
      end
    else
      return false
    end
  end

end
